<?php

class SiteDataKeywordFinder
{
  protected $_pdo;
    
  public function __construct()
  {   
    $this->_pdo = PDOFactory::getInstance();
  }
  
  public function findNotExisted()
  { 
    $query = $this->_pdo->query(
      'SELECT `sites`.*, `sites_keywords`.`keyword`, `sites_keywords`.`idKeyword` FROM `sites` '.
      ' INNER JOIN `sites_keywords` ON `sites_keywords`.`idSite` = `sites`.`idSite`'.
      ' LEFT JOIN `sites_data_keywords` ON `sites_data_keywords`.`idKeyword` = `sites_keywords`.`idKeyword`'.
      ' WHERE `sites_data_keywords`.`idKeywordData` IS NULL '
      );
    $query->setFetchMode(PDO::FETCH_ASSOC);
    $query->execute();    
    $count = 0;
    foreach( $query as $site ) {
      echo $site['keyword']." (idSite: ".$site['idSite'].")\n";
      $this->_createData($site);
      $count++;
    }
    return $count;
  }
  
  public function findOutdated()
  { 
    $query = $this->_pdo->query(
      'SELECT `sites`.*, `sites_keywords`.`keyword`, `sites_keywords`.`idKeyword` FROM `sites` '.
      ' INNER JOIN `sites_keywords` ON `sites_keywords`.`idSite` = `sites`.`idSite`'.
      ' LEFT JOIN `sites_data_keywords` ON `sites_data_keywords`.`idKeyword` = `sites_keywords`.`idKeyword`'.
      '  AND `sites_data_keywords`.`lastUpdated` > DATE_FORMAT(NOW(), "%Y-%m-%d 00:00:00")'.
      ' WHERE `sites_data_keywords`.`idKeywordData` IS NULL'
      );
    $query->setFetchMode(PDO::FETCH_ASSOC);
    $query->execute();    
    $count = 0;
    foreach( $query as $site ) {
      echo $site['keyword']." (idSite: ".$site['idSite'].")\n";
      $this->_createData($site);
      $count++;
    }
    return $count;
  }  

  protected function _createData($site)
  {
    $info = new GoogleSiteInfo($site['hostname']);
    $pos = $info->getPositionAtKeyword($site['keyword']);
    $set = array(
      'idKeyword' => $site['idKeyword'],
      'position' => (int)$pos
      );
    $sql = 'INSERT INTO `sites_data_keywords` (';
    foreach( array_keys($set) as $key ) {
      $sql .= ' `'.$key.'`,';
    }
    $sql = rtrim($sql,',');
    $sql .= ') VALUES (';
    foreach( array_keys($set) as $key ) {
      $sql .= ' :'.$key.',';
    }
    $sql = rtrim($sql,',');
    $sql .= ')';
    $state = $this->_pdo->prepare($sql);
    foreach( $set as $key => $v ) {
      if( is_string($v) ) {
        $state->bindValue(':'.$key,$v,PDO::PARAM_STR);
      } else {
        $state->bindValue(':'.$key,$v,PDO::PARAM_INT);
      }
    }
    $state->execute();
  }
}